#导入模块
import pymysql

#数据库连接
db=pymysql.connect(host="localhost",user="root",password="",db="studb",charset="utf8")
cursor=db.cursor()

#界面定义：
def welcome():
    print("="*15,"学生信息管理","="*17)
    print("{0:2} {1:18} {2:12}".format(" ","1. 学生信息查询","2. 学生信息添加"),"\n")
    print("{0:2} {1:18} {2:12}".format(" ","3. 学生信息删除","4. 退出"),"\n")
    print("="*46,"\n")

def key1():
    print("="*15,"学生信息查询","="*15)
    look()

def key2():
    global cursor
    print("="*15,"学生信息添加","="*15,)
    id1=input("请输入学生id号：")
    id_in=int(id1)
    name_in=input("请输入学生姓名：")
    sex_in=input("请输入学生性别：")
    age1=input("请输入学生年龄：")
    age_in=int(age1)
    classid_in=input("请输入学生班级：")
    data=(id_in,name_in,age_in,sex_in,classid_in)
    sql="insert into stu(id,name,age,sex,classid)values('%d','%s','%d','%s','%s')"%(data)
    try:
        cursor.execute(sql)
        db.commit()
        print("成功添加一条学生信息！")
        look()
    except Exception as err2:
        db.rollback()
        print("错误。。。",err2)

def key3():
    global cursor
    print("="*15,"学生信息删除","="*15)
    look()
    del1=input("请输入要删除学生id号：")
    del_in=int(del1)
    sql="delete from stu where id='%d'"%(del_in)
    try:
        cursor.execute(sql)
        db.commit()
        print("成功删除一条学生信息！")
        look()
    except Exception as err3:
        db.rollback()
        print("错误。。。",err3)

#信息查询显示
def look():
    global cursor
    sql="select * from stu"
    try:
        cursor.execute(sql)
        aa=cursor.fetchall()
        print("")
        print("|{0:<5}|{1:<8}|{2:<3}|{3:<3}|{4:<10}"
        .format("id","姓名","年龄","性别","班级"))
        print("-"*40)
        for vb in aa:
            print("|{0:<5}|{1:<10}|{2:<5}|{3:<5}|{4:<10}"
            .format(vb[0],vb[1],vb[2],vb[3],vb[4]))
            print("")
    except Exception as err1:
        print("错误。。。",err1)
#主程序
while True:
    welcome()
    print("")
    key_input=input("请输入对应的选择：")
    key=int(key_input)
    if key==1:
        key1()
        input("按回车键继续：")
        print("")
    elif key==2:
        key2()
        input("按回车键继续：")
        print("")
    elif key==3:
        key3()
        input("按回车键继续：")
        print("")
    elif key==4:
        print("="*17,"再见！","="*17)
        break
    else:
        print("="*15,"无效的指令！","="*15)
        print("")
        input("按回车键继续：")
        print("")

#关闭数据连接
db.close()